﻿Imports System.Data.OleDb
Module querysVentas
    Public Class C_querysVentas
        '************************************************************************************************************************
        'Obtencion de datos de reporte de llamadas
        '************************************************************************************************************************
        Public Function QxAsesorEntreFechas() As OleDbDataReader
            Using cadenaConexBD()
                Dim drQxAentreF As OleDbDataReader
                Dim fechaInicio As New OleDbParameter("@fechIni", SqlDbType.Date)
                fechaInicio.Value = frmInfoAsesorVentas.dtpInicio.Value
                Dim fechaFin As New OleDbParameter("@fechFin", SqlDbType.Date)
                fechaFin.Value = frmInfoAsesorVentas.dtpfin.Value
                Dim bucarAuditadoxId As New OleDbCommand("Select id_ado from auditado where noap_ado = '" & frmInfoAsesorVentas.cbo_asesor.Text & "'", cadenaConexBD)
                Dim idAuditado = bucarAuditadoxId.ExecuteScalar()
                Dim instruccion As New OleDbCommand("SELECT llamada_vta.fau_llam, llamada_vta.fec_llam, llamada_vta.nlot_llam, tipo_llamada.desc_tllam, auditor.noap_au, auditado.noap_ado, empresa.nomb_empr, saludo_vta.opt_salu, empatia_vta.opt_emp, indagacion_vta.opt_ind, manejo_vta.opt_mane, entusiasmo_vta.opt_entu, speech_vta.opt_spe, presentacion_vta.opt_pres, mecanismo_vta.opt_mec, pide_vta.opt_pide, llamada_vta.tfav_llam, llamada_vta.tctra_llam, llamada_vta.vato_llam, llamada_vta.idunico_llam,llamada_vta.caso_llam FROM (((((((((((((llamada_vta Inner Join tipo_llamada ON((llamada_vta.id_tllam = tipo_llamada.id_tllam))) Inner Join auditor ON((llamada_vta.id_au = auditor.id_au))) Inner Join auditado ON((llamada_vta.id_ado = auditado.id_ado))) Inner Join saludo_vta ON((llamada_vta.vaop_salu = saludo_vta.vaop_salu))) Inner Join empatia_vta ON((llamada_vta.vaop_emp = empatia_vta.vaop_emp))) Inner Join indagacion_vta ON((llamada_vta.vaop_ind = indagacion_vta.vaop_ind))) Inner Join manejo_vta ON((llamada_vta.vaop_man = manejo_vta.vaop_mane))) Inner Join entusiasmo_vta ON((llamada_vta.vaop_ent = entusiasmo_vta.vaop_entu))) Inner Join speech_vta ON((llamada_vta.vaop_spe = speech_vta.vaop_spe))) Inner Join presentacion_vta ON((llamada_vta.vaop_pre = presentacion_vta.vaop_pres))) Inner Join mecanismo_vta ON((llamada_vta.vaop_mec = mecanismo_vta.vaop_mec))) Inner Join pide_vta ON((llamada_vta.vaop_pide = pide_vta.vaop_pide))) Inner Join empresa ON((auditado.id_empr = empresa.id_empr))) WHERE ((llamada_vta.fau_llam BETWEEN @fechIni AND @fechFin) AND (llamada_vta.id_ado = " & idAuditado & "))", cadenaConexBD)
                instruccion.Parameters.Add(fechaInicio)
                instruccion.Parameters.Add(fechaFin)
                drQxAentreF = instruccion.ExecuteReader()
                Return drQxAentreF
                drQxAentreF.Close()
            End Using
        End Function
        '************************************************************************************************************************
        'Insercion de datos en tabla temporal
        '************************************************************************************************************************
        Public Sub insertarTemporal(ByVal v01 As String, ByVal v02 As String, ByVal v03 As String, ByVal v04 As String, ByVal v05 As String, ByVal v06 As String, ByVal v07 As String, ByVal v08 As String, ByVal v09 As String, ByVal v10 As String, ByVal v11 As String, ByVal v12 As String, ByVal v13 As String, ByVal v14 As String, ByVal v15 As String, ByVal v16 As String, ByVal v17 As String, ByVal v18 As String, ByVal v19 As Double, ByVal v20 As String, ByVal v21 As String)
            Try
                cadenaConexBD()
                'inserccion de datos a la tabla
                Dim instruccion As New OleDbCommand("Insert into temporal_reportes_vta (fecha_auditoria, fecha_llamada, numero_lote, tipo_llamada, auditor, auditado, empresa, saludo, empatia, indagacion, disposicion, compromiso, informacion, comunicacion, credibilidad, cierre, a_favor, en_contra, puntos, idunico_llam,caso_llam) values ('" & v01 & "','" & v02 & "','" & v03 & "','" & v04 & "','" & v05 & "','" & v06 & "','" & v07 & "','" & v08 & "','" & v09 & "','" & v10 & "','" & v11 & "','" & v12 & "','" & v13 & "','" & v14 & "','" & v15 & "','" & v16 & "','" & v17 & "','" & v18 & "','" & v19 & "','" & v20 & "','" & v21 & "')", cadenaConexBD)
                instruccion.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                cadenaConexBD.Close()
            End Try
        End Sub
        '************************************************************************************************************************
        'Reporte de datos de llamadas por empresas
        '************************************************************************************************************************
        Public Function QxEmpresaEntreFechas() As OleDbDataReader
            Using cadenaConexBD()
                Dim drQxEentreF As OleDbDataReader
                'parametrizo las fechas de inicio y fin del informe
                Dim fechaInicio As New OleDbParameter("@fechIni", SqlDbType.Date)
                fechaInicio.Value = frmInfoEmprVentas.dtpInicio.Value
                Dim fechaFin As New OleDbParameter("@fechFin", SqlDbType.Date)
                fechaFin.Value = frmInfoEmprVentas.dtpfin.Value
                'parametro del auditado
                Dim bucarEmpresaxId As New OleDbCommand("SELECT id_empr FROM empresa WHERE nomb_empr = '" & frmInfoEmprVentas.cbo_empresa.Text & "'", cadenaConexBD)
                Dim idEmpresa = bucarEmpresaxId.ExecuteScalar()
                Dim instruccion As New OleDbCommand("SELECT llamada_vta.fau_llam, llamada_vta.fec_llam, llamada_vta.nlot_llam, tipo_llamada.desc_tllam, auditor.noap_au, auditado.noap_ado, empresa.nomb_empr, saludo_vta.opt_salu, empatia_vta.opt_emp, indagacion_vta.opt_ind, manejo_vta.opt_mane, entusiasmo_vta.opt_entu, speech_vta.opt_spe, presentacion_vta.opt_pres, mecanismo_vta.opt_mec, pide_vta.opt_pide, llamada_vta.tfav_llam, llamada_vta.tctra_llam, llamada_vta.vato_llam, llamada_vta.idunico_llam, llamada_vta.caso_llam FROM (((((((((((((llamada_vta Inner Join tipo_llamada ON((llamada_vta.id_tllam = tipo_llamada.id_tllam))) Inner Join auditor ON((llamada_vta.id_au = auditor.id_au))) Inner Join auditado ON((llamada_vta.id_ado = auditado.id_ado))) Inner Join saludo_vta ON((llamada_vta.vaop_salu = saludo_vta.vaop_salu))) Inner Join empatia_vta ON((llamada_vta.vaop_emp = empatia_vta.vaop_emp))) Inner Join indagacion_vta ON((llamada_vta.vaop_ind = indagacion_vta.vaop_ind))) Inner Join manejo_vta ON((llamada_vta.vaop_man = manejo_vta.vaop_mane))) Inner Join entusiasmo_vta ON((llamada_vta.vaop_ent = entusiasmo_vta.vaop_entu))) Inner Join speech_vta ON((llamada_vta.vaop_spe = speech_vta.vaop_spe))) Inner Join presentacion_vta ON((llamada_vta.vaop_pre = presentacion_vta.vaop_pres))) Inner Join mecanismo_vta ON((llamada_vta.vaop_mec = mecanismo_vta.vaop_mec))) Inner Join pide_vta ON((llamada_vta.vaop_pide = pide_vta.vaop_pide))) Inner Join empresa ON((llamada_vta.id_empr = empresa.id_empr))) WHERE ((llamada_vta.fau_llam BETWEEN @fechIni AND @fechFin) AND (llamada_vta.id_empr = " & idEmpresa & "))", cadenaConexBD)
                instruccion.Parameters.Add(fechaInicio)
                instruccion.Parameters.Add(fechaFin)
                drQxEentreF = instruccion.ExecuteReader()
                Return drQxEentreF
                drQxEentreF.Close()
            End Using
        End Function
        '************************************************************************************************************************
        'Insercion de datos en tabla temporal
        '************************************************************************************************************************
        Public Sub insertarTemporalEmpresa(ByVal v01 As String, ByVal v02 As String, ByVal v03 As String, ByVal v04 As String, ByVal v05 As String, ByVal v06 As String, ByVal v07 As String, ByVal v08 As String, ByVal v09 As String, ByVal v10 As String, ByVal v11 As String, ByVal v12 As String, ByVal v13 As String, ByVal v14 As String, ByVal v15 As String, ByVal v16 As String, ByVal v17 As String, ByVal v18 As String, ByVal v19 As Double, ByVal v20 As String, ByVal v21 As String)
            Try
                cadenaConexBD()
                'inserccion de datos a la tabla "temporal_empresa_at"
                Dim instruccion As New OleDbCommand("Insert into temporal_empresa_vta (fecha_auditoria, fecha_llamada, numero_lote, tipo_llamada, auditor, auditado, empresa, saludo, empatia, indagacion, disposicion, compromiso, informacion, comunicacion, credibilidad, cierre, a_favor, en_contra, puntos, idunico_llam, caso_llam) values ('" & v01 & "','" & v02 & "','" & v03 & "','" & v04 & "','" & v05 & "','" & v06 & "','" & v07 & "','" & v08 & "','" & v09 & "','" & v10 & "','" & v11 & "','" & v12 & "','" & v13 & "','" & v14 & "','" & v15 & "','" & v16 & "','" & v17 & "','" & v18 & "','" & v19 & "','" & v20 & "','" & v21 & "')", cadenaConexBD)
                instruccion.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                cadenaConexBD.Close()
            End Try
        End Sub
        '************************************************************************************************************************
        'vaciar la tabla temporal cuando se generen nuevos informes
        '************************************************************************************************************************
        Public Sub eliminarTemporales()
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("delete * from temporal_reportes_vta ", cadenaConexBD)
                instruccion.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                cadenaConexBD.Close()
            End Try
        End Sub
        '************************************************************************************************************************
        'vaciar la tabla temporal cuando se generen nuevos informes POR EMPRESA
        '************************************************************************************************************************
        Public Sub eliminarTemporalesPorEmpresaAT()
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("delete * from temporal_empresa_vta ", cadenaConexBD)
                instruccion.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                cadenaConexBD.Close()
            End Try
        End Sub
        '------------------------------------------------------------------------------------------------------------------------------------
    End Class

End Module
